package com.deng.maintenance.data;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.deng.common.SQLHelper;
import com.deng.common.vo.DutyTypeVo;
import com.deng.maintenance.vo.ItemWeightVo;
import com.deng.maintenance.vo.KPIValueVOOld;
import com.deng.maintenance.vo.KPI_DicVO;
import com.deng.maintenance.vo.Stat_OrgVO;
import com.deng.util.DateTools;

public class MaintenanceData extends SQLHelper{

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		MaintenanceData da=new MaintenanceData();
		KPI_DicVO vo=(KPI_DicVO)da.searchKpi_Dic(-1, "DK001").get(0);
		System.out.println(vo.weight);
	}
	
	public List<Stat_OrgVO> searchSataOrg(String newCode,String parent_id) {
		// TODO Auto-generated method stub
		List<Stat_OrgVO> ret =new ArrayList<Stat_OrgVO>();
		String select ="select * from t_sys_stat_department";
		
		String where="";
		if(newCode!=null&&!"".equals(newCode)){
			where +=" newCode='"+newCode+"'";
		}
		if(parent_id!=null&&!"".equals(parent_id)){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" parent_id="+parent_id;
		}
		if(!"".equals(select)){
			where =" where "+where;
		}
		
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,select+where);
			
			while(rs.next()){
				Stat_OrgVO vo = new Stat_OrgVO();
				vo.setApply_flag(rs.getInt("apply_flag"));
				vo.setBase_bonus_function(rs.getString("base_bonus_function"));
				vo.setBase_bonus_function_detail(rs.getString("base_bonus_function_detail"));
				vo.setDivisionTypeCode(rs.getString("divisionTypeCode"));
				vo.setFunction_name(rs.getString("function_name"));
				vo.setId(rs.getInt("id"));
				vo.setIoType(rs.getString("ioType"));
				vo.setIs_leaf(rs.getInt("is_leaf"));
				vo.setName(rs.getString("name"));
				vo.setNewCode(rs.getString("newCode"));
				vo.setParent_id(rs.getInt("parent_id"));
				vo.setSjly(rs.getInt("sjly"));
				ret.add(vo);
			}
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return ret;
	}
	
	public List<DutyTypeVo> searchDutyType(int id) {
		// TODO Auto-generated method stub
		List<DutyTypeVo> ret =new ArrayList<DutyTypeVo>();
		String select ="select id,name from DICT_DUTY_TYPE";
		if(id>0){
			select +=" where id="+id;
		}
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,select);
			
			while(rs.next()){
				DutyTypeVo vo = new DutyTypeVo();
				vo.id =rs.getInt("id");
				vo.name = rs.getString("name");
				ret.add(vo);
			}
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return ret;
	}
	
	
	public List<KPI_DicVO> searchKpi_Dic(Integer kpi_id ,String dep_id) {
		// TODO Auto-generated method stub
		List<KPI_DicVO> ret =new ArrayList<KPI_DicVO>();
		String select ="select * from kpi_dic";
		String where="";
		if(kpi_id>=0){
			where +=" kpi_id="+kpi_id;
		}
		if(dep_id!=null&&!"".equals(dep_id)){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" dep_id='"+dep_id+"'";
		}
		if(!"".equals(where)){
			where =" where "+where;
		}
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,select+where+" order by kpi_id");
			
			while(rs.next()){
				KPI_DicVO vo = new KPI_DicVO();
				vo.setAudi_freq(rs.getString("audi_freq"));
				vo.setDep_id(rs.getString("dep_id"));
				vo.setExpression(rs.getString("expression"));
				vo.setKpi_define(rs.getString("kpi_define"));
				vo.setKpi_id(rs.getInt("kpi_id"));
				vo.setKpi_name(rs.getString("kpi_name"));
				vo.setScore_method(rs.getString("score_method"));
				vo.setTarget_value(rs.getDouble("target_value")+"");
				
				if(rs.getDouble("target_value_max")!=0){
					vo.setTarget_value_max(rs.getDouble("target_value_max")+"");
				}
				else{
					vo.setTarget_value_max("");
				}
				vo.setWeight(rs.getDouble("weight")+"");
				vo.setCreate_time(rs.getTimestamp("create_time"));
				vo.setModify_time(rs.getTimestamp("modify_time"));
				vo.setDuty_id(rs.getInt("duty_id"));
				vo.setDuty_name(rs.getString("duty_name"));
				ret.add(vo);
			}
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return ret;
	}
	
	
	public String insertKpi_Dic(KPI_DicVO vo) {
		// TODO Auto-generated method stub
		String ret ="succeed";
		String sql ="insert into kpi_dic(audi_freq,dep_id,expression,kpi_define,kpi_name," +
				"score_method,target_value,weight,create_time,modify_time," +
				"duty_id,duty_name,target_value_max) " +
				"values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection con = getConnect();
		String selDutyName = "select name from DICT_DUTY_TYPE where id = "+vo.duty_id;
		ResultSet rs = queryData(con, selDutyName);
		
		try{
			if(rs.next()){
				vo.duty_name = rs.getString("name");
			}
			
			con.setAutoCommit(false);
			PreparedStatement pst=con.prepareStatement(sql);
			pst.setString(1, vo.getAudi_freq());
			pst.setString(2, vo.getDep_id());
			pst.setString(3, vo.getExpression());
			pst.setString(4, vo.getKpi_define());
			pst.setString(5, vo.getKpi_name());
			pst.setString(6, vo.getScore_method());
			pst.setDouble(7, Double.valueOf(vo.getTarget_value()));
			pst.setDouble(8, Double.valueOf(vo.getWeight()));
			pst.setTimestamp(9, DateTools.getTimestamp(new Date()));
			pst.setTimestamp(10, DateTools.getTimestamp(new Date()));
			pst.setInt(11, vo.getDuty_id());
			pst.setString(12, vo.getDuty_name());
			if(vo.getTarget_value_max()!=null&&!"".equals(vo.getTarget_value_max())){
				pst.setDouble(13, Double.valueOf(vo.getTarget_value_max()));
			}
			else{
				pst.setNull(13, java.sql.Types.DOUBLE);
			}
			pst.executeUpdate();
			pst.close();
			con.commit();
		}catch(Exception ex){
			ret="faild";
			ex.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		finally{
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ret;
	}
	
	public String updateKpi_Dic(KPI_DicVO vo) {
		// TODO Auto-generated method stub
		String ret ="succeed";
		String sql ="update kpi_dic set audi_freq=?,dep_id=?,expression=?,kpi_define=?,kpi_name=?,score_method=?,target_value=?,weight=?,modify_time=?,duty_id=?,duty_name=?,target_value_max=? where kpi_id=? ";
		Connection con = getConnect();
		String selDutyName = "select name from DICT_DUTY_TYPE where id = "+vo.duty_id;
		ResultSet rs = queryData(con, selDutyName);
		try{
			if(rs.next()){
				vo.duty_name = rs.getString("name");
			}
			con.setAutoCommit(false);
			PreparedStatement pst=con.prepareStatement(sql);
			pst.setString(1, vo.getAudi_freq());
			pst.setString(2, vo.getDep_id());
			pst.setString(3, vo.getExpression());
			pst.setString(4, vo.getKpi_define());
			pst.setString(5, vo.getKpi_name());
			pst.setString(6, vo.getScore_method());
			pst.setDouble(7, Double.valueOf(vo.getTarget_value()));
			pst.setDouble(8, Double.valueOf(vo.getWeight()));
			pst.setTimestamp(9, DateTools.getTimestamp(new Date()));
			pst.setInt(10, vo.getDuty_id());
			pst.setString(11, vo.getDuty_name());
			
			if(vo.getTarget_value_max()!=null&&!"".equals(vo.getTarget_value_max())){
				pst.setDouble(12, Double.valueOf(vo.getTarget_value_max()));
			}
			else{
				pst.setNull(12, java.sql.Types.DOUBLE);
			}
			pst.setInt(13, vo.getKpi_id());
			
			pst.executeUpdate();
			pst.close();
			con.commit();
		}catch(Exception ex){
			ret="faild";
			ex.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		finally{
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ret;
	}
	
	
	public List<KPIValueVOOld> searchKPIValues(String dep_id,String duty_id,String year,
			String fre_code,String type){
		List<KPIValueVOOld> ret=new ArrayList<KPIValueVOOld>();
		String select ="select (select a.kpi_name from kpi_dic a where a.kpi_id=kpi_value.kpi_id) as kpi," +
				"(select a.expression from kpi_dic a where a.kpi_id=kpi_value.kpi_id) as kpi_exp," +
				"(select a.target_value from kpi_dic a where a.kpi_id=kpi_value.kpi_id) as target_value," +
				"(select a.target_value_max from kpi_dic a where a.kpi_id=kpi_value.kpi_id) as target_value_max," +
				"(select a.weight from kpi_dic a where a.kpi_id=kpi_value.kpi_id) as weight," +
				"(select a.score_method from kpi_dic a where a.kpi_id=kpi_value.kpi_id) as score_method," +
				"(select a.kpi_define from kpi_dic a where a.kpi_id=kpi_value.kpi_id) as kpi_define,* from kpi_value";
		String where="";
		if(dep_id!=null&&!"".equals(dep_id)){
			where +=" dep_id='"+dep_id+"'";
		}
		if(duty_id!=null&&!"".equals(duty_id)){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" duty_id="+duty_id;
		}
		if(year!=null&&!"".equals(year)){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" year="+year;
		}
		if(fre_code!=null&&!"".equals(fre_code)){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" frq_code='"+fre_code+"'";
			
			if(type!=null&&!"".equals(type)){
				if(!"".equals(where)){
					where+=" and ";
				}
				
				if("0001".equals(fre_code)){
					where +=" year1='"+type+"'";
				}
				else if("0002".equals(fre_code)){
					where +=" half_year='"+type+"'";
				}
				else if("0003".equals(fre_code)){
					where +=" quarter='"+type+"'";
				}
				else if("0004".equals(fre_code)){
					where +=" month='"+type+"'";
				}
			}
			
		}
		if(!"".equals(where)){
			where =" where "+where;
		}
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,select+where);
			
			while(rs.next()){
				KPIValueVOOld vo = new KPIValueVOOld();
				vo.setCreate_time(rs.getTimestamp("create_time"));
				vo.setDep_id(rs.getString("dep_id"));
				vo.setFrq_code(rs.getString("frq_code"));
				vo.setId(rs.getInt("id"));
				vo.setKpi_id(rs.getInt("kpi_id"));
				vo.setKpi_input_value(rs.getDouble("kpi_input_value")+"");
				vo.setKpi_score(rs.getDouble("kpi_score")+"");
				vo.setModify_time(rs.getTimestamp("modify_time"));
				vo.setYear(rs.getInt("year"));
				vo.setDuty_id(rs.getInt("duty_id"));
				vo.setQuarter(rs.getString("quarter"));
				vo.setMonth(rs.getString("month"));
				vo.setKpi_name(rs.getString("kpi"));
				vo.setExp(rs.getString("kpi_exp"));
				vo.setHalf_year(rs.getString("half_year"));
				vo.setYear1(rs.getString("year1"));
				vo.setTarget_value(rs.getDouble("target_value")+"");
				
				if(rs.getDouble("target_value_max")!=0){
					vo.setTarget_value_max(rs.getDouble("target_value_max")+"");
				}
				else{
					vo.setTarget_value_max("");
				}
				
				vo.setWeight(rs.getDouble("weight")+"");
				vo.setScore_method(rs.getString("score_method"));
				vo.setKpi_define(rs.getString("kpi_define"));
				vo.setStatus(rs.getString("status"));
				ret.add(vo);
			}
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		
		
		return ret;
	}
	
	
	
	public List<KPIValueVOOld> searchKPIValues(KPIValueVOOld vo_p){
		List<KPIValueVOOld> ret=new ArrayList<KPIValueVOOld>();
		String select ="select * from kpi_value";
		String where="";
		
		if(vo_p.getDep_id()!=null&&!"".equals(vo_p.getDep_id())){
			where +=" dep_id='"+vo_p.getDep_id()+"'";
		}
		if(vo_p.getKpi_id()!=null&&!"".equals(vo_p.getKpi_id())){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" kpi_id="+vo_p.getKpi_id();
		}
		if(vo_p.getDuty_id()!=null&&!"".equals(vo_p.getDuty_id())){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" duty_id="+vo_p.getDuty_id();
		}
		if(vo_p.getYear()!=null&&!"".equals(vo_p.getYear())){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" year="+vo_p.getYear();
		}
		if(vo_p.getFrq_code()!=null&&!"".equals(vo_p.getFrq_code())){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" frq_code='"+vo_p.getFrq_code()+"'";
		}
		
		if(vo_p.getQuarter()!=null&&!"".equals(vo_p.getQuarter())){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" quarter='"+vo_p.getQuarter()+"'";
		}
		if(vo_p.getHalf_year()!=null&&!"".equals(vo_p.getHalf_year())){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" half_year='"+vo_p.getHalf_year()+"'";
		}
		if(vo_p.getMonth()!=null&&!"".equals(vo_p.getMonth())){
			if(!"".equals(where)){
				where+=" and ";
			}
			where +=" month='"+vo_p.getMonth()+"'";
		}
		
		if(!"".equals(where)){
			where =" where "+where;
		}
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,select+where);
			
			while(rs.next()){
				KPIValueVOOld vo = new KPIValueVOOld();
				vo.setCreate_time(rs.getTimestamp("create_time"));
				vo.setDep_id(rs.getString("dep_id"));
				vo.setFrq_code(rs.getString("frq_code"));
				vo.setId(rs.getInt("id"));
				vo.setKpi_id(rs.getInt("kpi_id"));
				vo.setKpi_input_value(rs.getDouble("kpi_input_value")+"");
				vo.setKpi_score(rs.getDouble("kpi_score")+"");
				vo.setModify_time(rs.getTimestamp("modify_time"));
				vo.setYear(rs.getInt("year"));
				vo.setDuty_id(rs.getInt("duty_id"));
				vo.setQuarter(rs.getString("quarter"));
				vo.setMonth(rs.getString("month"));
				vo.setHalf_year(rs.getString("half_year"));
				vo.setYear1(rs.getString("year1"));
				vo.setStatus(rs.getString("status"));
				ret.add(vo);
			}
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		
		
		return ret;
	}
	
	
	public List<KPIValueVOOld> searchKpi_Dic(String dep_id,String duty_id,String audi_freq,String year,String type) {
		// TODO Auto-generated method stub
		List<KPIValueVOOld> ret =new ArrayList<KPIValueVOOld>();
		String select ="select * from kpi_dic";
		String where="";
		String where1="";
		if(dep_id!=null&&!"".equals(dep_id)){
			where +=" dep_id='"+dep_id+"'";
			where1+="  kpi_dic.dep_id=a.dep_id ";
		}
		if(duty_id!=null&&!"".equals(duty_id)){
			if(!"".equals(where)){
				where+=" and ";
			}
			if(!"".equals(where1)){
				where1+=" and ";
			}
			where +=" duty_id="+duty_id;
			where1+=" kpi_dic.duty_id=a.duty_id ";
		}
		if(audi_freq!=null&&!"".equals(audi_freq)){
			if(!"".equals(where)){
				where+=" and ";
			}
			if(!"".equals(where1)){
				where1+=" and ";
			}
			where +=" audi_freq='"+audi_freq+"'";
			where1+=" kpi_dic.audi_freq=a.frq_code ";
			
			if(type!=null&&!"".equals(type)){
				if(!"".equals(where1)){
					where1+=" and ";
				}
				
				if("0001".equals(audi_freq)){
					where1 +=" year1='"+type+"'";
				}
				else if("0002".equals(audi_freq)){
					where1 +=" half_year='"+type+"'";
				}
				else if("0003".equals(audi_freq)){
					where1 +=" quarter='"+type+"'";
				}
				else if("0004".equals(audi_freq)){
					where1 +=" month='"+type+"'";
				}
			}
		}
		if(year!=null&&!"".equals(year)){
			if(!"".equals(where1)){
				where1+=" and ";
			}
			where1+=" a.year="+year;
		}
		if(!"".equals(where) || !"".equals(where1)){
			where =" where "+where;
		}
		if(!"".equals(where1)){
			where1=" and "+where1;
		}
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,select+where+" and not exists(select null from kpi_value a where kpi_dic.kpi_id=a.kpi_id "+where1+")");
			
			while(rs.next()){
				KPIValueVOOld vo = new KPIValueVOOld();
				
				vo.setId(-1);
				vo.setKpi_id(rs.getInt("kpi_id"));
				
				vo.setDuty_id(rs.getInt("duty_id"));
				
				vo.setDep_id(rs.getString("dep_id"));
				vo.setFrq_code(rs.getString("audi_freq"));
				
				vo.setKpi_name(rs.getString("kpi_name"));
				vo.setExp(rs.getString("expression"));
				vo.setTarget_value(rs.getDouble("target_value")+"");
				if(rs.getDouble("target_value_max")!=0){
					vo.setTarget_value_max(rs.getDouble("target_value_max")+"");
				}
				else{
					vo.setTarget_value_max("");
				}
				vo.setWeight(rs.getDouble("weight")+"");
				vo.setKpi_define(rs.getString("kpi_define"));
				vo.setScore_method(rs.getString("score_method"));
				vo.setStatus("0");
				if(year!=null&&!"".equals(year)){
					vo.setYear(Integer.valueOf(year));
				}
				if("0001".equals(audi_freq)){
					vo.setYear1(type);
				}
				else if("0002".equals(audi_freq)){
					vo.setHalf_year(type);
				}
				else if("0003".equals(audi_freq)){
					vo.setQuarter(type);
				}
				else if("0004".equals(audi_freq)){
					vo.setMonth(type);
				}
				ret.add(vo);
			}
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return ret;
	}
	
	
	public String insertKPIValues(List<KPIValueVOOld> vos) {
		// TODO Auto-generated method stub
		String ret ="succeed";
		String sql ="insert into kpi_value(dep_id,kpi_id,kpi_input_value,kpi_score,create_time,modify_time,frq_code,year,duty_id,quarter,month,half_year,year1,status) " +
				"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection con = getConnect();
		
		try{
			con.setAutoCommit(false);
			PreparedStatement pst=con.prepareStatement(sql);
			for(int i=0;i<vos.size();i++){
				KPIValueVOOld vo=vos.get(i);
				pst.setString(1, vo.getDep_id());
				pst.setInt(2, vo.getKpi_id());
				pst.setDouble(3, Double.valueOf(vo.getKpi_input_value()));
				pst.setDouble(4, Double.valueOf(vo.getKpi_score()));
				Timestamp time=DateTools.getTimestamp(new Date());
				pst.setTimestamp(5, time);
				pst.setTimestamp(6, time);
				pst.setString(7, vo.getFrq_code());
				pst.setInt(8, vo.getYear());
				pst.setInt(9, vo.getDuty_id());
				pst.setString(10, vo.getQuarter());
				pst.setString(11, vo.getMonth());
				pst.setString(12, vo.getHalf_year());
				pst.setString(13, vo.getYear1());
				pst.setString(14, vo.getStatus());
				pst.addBatch();
			}
			pst.executeBatch();
			pst.close();
			con.commit();
		}catch(Exception ex){
			ret="faild";
			ex.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		finally{
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ret;
	}
	
	public String updateKPIValues(List<KPIValueVOOld> vos) {
		// TODO Auto-generated method stub
		String ret ="succeed";
		String sql ="update kpi_value set dep_id=?,kpi_id=?,kpi_input_value=?,kpi_score=?" +
				",modify_time=?,frq_code=?,year=?,duty_id=?,quarter=?,month=?,half_year=?,year1=?,status=? where id=? ";
		Connection con = getConnect();
		
		try{
			con.setAutoCommit(false);
			PreparedStatement pst=con.prepareStatement(sql);
			for(int i=0;i<vos.size();i++){
				KPIValueVOOld vo=(KPIValueVOOld)vos.get(i);
				pst.setString(1, vo.getDep_id());
				pst.setInt(2, vo.getKpi_id());
				pst.setDouble(3, Double.valueOf(vo.getKpi_input_value()));
				pst.setDouble(4, Double.valueOf(vo.getKpi_score()));
				Timestamp time=DateTools.getTimestamp(new Date());
				pst.setTimestamp(5, time);
				pst.setString(6, vo.getFrq_code());
				pst.setInt(7, vo.getYear());
				pst.setInt(8, vo.getDuty_id());
				pst.setString(9, vo.getQuarter());
				pst.setString(10, vo.getMonth());
				pst.setString(11, vo.getHalf_year());
				pst.setString(12, vo.getYear1());
				pst.setString(13, vo.getStatus());
				pst.setInt(14, vo.getId());
				pst.addBatch();
			}
			pst.executeBatch();
			pst.close();
			con.commit();
		}catch(Exception ex){
			ret="faild";
			ex.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		finally{
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ret;
	}

}
